﻿import time
start = time.clock()
import xlsxwriter, xlrd
import csv
import sys, os.path
defect_code = {}
panel_grade = {}
defect_data={}
zh={}
gy={}
sorted_data=[]
data=[]
def load_data(file):
    print('读取data文件...')
    global defect_code,panel_grade,test,grade,grade_count,model,tr,data
    try:
        file_open = open(file, 'r')
        reader = csv.reader(file_open)
    except:
        print('data文件不存在')
    for line in reader:
        temptemp = [line[4], line[13], line[15]]
        if temptemp not in data:
            print(temptemp)
            data.append(temptemp)
            try:
                panel_grade[temptemp[1]]+=1
            except:
                panel_grade[temptemp[1]]=1
            try:
                defect_code[temptemp[2]]+=1
            except:
                defect_code[temptemp[2]]=1
    grade_count =[]
    grade=['S','A','T','F','E']
    for i in range(5):
        try:
            grade_count.append(panel_grade[grade[i]])
        except:
            grade_count.append(0)
    tr= len(data)-1
    test = tr-grade_count[4]
    model=data[10][0][:4]

def Classification():
    print('分析Classification文件...')
    global zh,gy
    try:
        data = xlrd.open_workbook('Classification.xlsx')
    except:
        print('Classification文件不存在')
    table = data.sheet_by_index(0)
    nrows = table.nrows
    ncols = table.ncols
    for i in range(nrows):
        zh[table.cell_value(i,0)]=table.cell_value(i,2)
        gy[table.cell_value(i,2)]=table.cell_value(i,1)

def analysis():
    print('数据处理...')
    global sorted_data,defect_code
    for i in defect_code:
        try:
            defect_data[zh[i]]=defect_code[i]
        except:
            continue
    sorted_data=sorted(defect_data.items(),key=lambda defect_data:defect_data[1],reverse=True)

def write_data(writename):
    print('写入文件excel...')
    global sorted_data,grade_count,gy,test,model
    x = 1
    y = 3
    z=1
    offset = [1,1,1,1]
    rate=[0,0,0,0]
    fd={'BP':'0','EVEN':'1','EAC':'2','ETC':'3'}
    statistical = (['型号', model], ['Input',tr], ['检测', test])
    workbook = xlsxwriter.Workbook(writename)
    worksheet = workbook.add_worksheet()
    def formatset(format,font,size):
        format.set_font_name(font)
        format.set_font_size(size)
        format.set_align('center')
        format.set_align('vcenter')
        format.set_border(1)
    title = workbook.add_format()
    name = workbook.add_format()
    coderate= workbook.add_format()
    coderate.set_num_format(0x0a)
    formatset(title,'Arial',18,)
    formatset(name, '微软雅黑', 9,)
    formatset(coderate, '微软雅黑', 9,)
    worksheet.merge_range('B5:B6', 'BP',title)
    worksheet.merge_range('B8:B9', 'EVEN', title)
    worksheet.merge_range('B11:B12', 'EAC',title)
    worksheet.merge_range('B14:B15', 'ETC',title)
    for i in range(len(sorted_data)):
        m=int(fd[gy[sorted_data[i][0]]])
        n = offset[m]
        offset[m] = offset[m] + 1
        temprate=sorted_data[i][1]/test
        rate[m]=rate[m]+temprate
        worksheet.write(y+3*m+1, x+n, sorted_data[i][0],name)
        worksheet.write(y+3*m+2, x+n, sorted_data[i][1],name)
        worksheet.write(y+3*m+3, x+n, temprate,coderate)
    for item,number in statistical:
        worksheet.write(z,x, item, name)
        worksheet.write(z,x+1, number, name)
        z+=1
    for i in range(0,4):
        worksheet.write(y+3*i+3, x, rate[i], coderate)
    for i in range(5):
        worksheet.write(y - 2, x+i+2, grade[i],name)
        worksheet.write(y - 1, x + i+2, grade_count[i], name)
        worksheet.write(y, x + i+2, grade_count[i]/test, coderate)
    workbook.close()

Classification()
load_data('data.csv')
analysis()
write_data('result.xlsx')

elapsed = (time.clock() - start)
print('完成，用时:{}秒'.format(elapsed))
input("回车退出")